﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class TaiKhoanDAO
    {
        #region lay tat ca tai khoan
        public static List<TaiKhoanDTO> getAll()
        {
            SqlConnection connect = null;
            List<TaiKhoanDTO> list = new List<TaiKhoanDTO>();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from taikhoan";
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    TaiKhoanDTO tk = new TaiKhoanDTO();
                    tk.Id = reader.GetInt32(0);
                    tk.TenTaiKhoan = reader.GetString(1);
                    tk.MatKhau = reader.GetString(2);
                    tk.DiaChi = reader.GetString(3);
                    tk.Email = reader.GetString(4);
                    tk.Phone = reader.GetString(5);
                    tk.LoaiTaiKhoan = reader.GetInt32(6);
                    list.Add(tk);
                }
            }
            catch (Exception ex)
            {
                list = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return list;
        }
        #endregion

        #region lay tai khoan theo ten tai khoan
        public static TaiKhoanDTO getAccount(string tenDangNhap)
        {
            SqlConnection connect = null;
            TaiKhoanDTO tk = new TaiKhoanDTO();
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from taikhoan where tentaikhoan=N'" + tenDangNhap + "'";
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    tk.Id = reader.GetInt32(0);
                    tk.TenTaiKhoan = reader.GetString(1);
                    tk.MatKhau = reader.GetString(2);
                    tk.DiaChi = reader.GetString(3);
                    tk.Email = reader.GetString(4);
                    tk.Phone = reader.GetString(5);
                    tk.LoaiTaiKhoan = reader.GetInt32(6);
                }
            }
            catch (Exception ex)
            {
                tk = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return tk;
        }
        #endregion

        #region lay tai khoan
        public static TaiKhoanDTO logIn(string tenDangNhap, string matKhau)
        {
            SqlConnection connect = null;
            TaiKhoanDTO tk = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "select * from taikhoan where tentaikhoan=N'" + tenDangNhap + "' and matkhau=N'" + matKhau + "'";
                SqlCommand command = new SqlCommand(strCommand, connect);
                SqlDataReader reader = command.ExecuteReader();

                if (reader.Read())
                {
                    tk = new TaiKhoanDTO();
                    tk.Id = reader.GetInt32(0);
                    tk.TenTaiKhoan = reader.GetString(1);
                    tk.MatKhau = reader.GetString(2);
                    tk.DiaChi = reader.GetString(3);
                    tk.Email = reader.GetString(4);
                    tk.Phone = reader.GetString(5);
                    tk.LoaiTaiKhoan = reader.GetInt32(6);
                }
            }
            catch (Exception ex)
            {
                tk = null;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return tk;
        }
        #endregion

        #region kiem tra ton tai tai khoan
        public static bool isExistAccount(string _username)
        {
            bool result = false;
            List<TaiKhoanDTO> list = getAll();
            if (list == null)
                return result;
            foreach (TaiKhoanDTO tk in list)
            {
                if (tk.TenTaiKhoan.Equals(_username))
                {
                    result = true;
                    break;
                }
            }
            return result;
        }
        #endregion

        #region them tai khoan
        public static bool insertAccount(TaiKhoanDTO tk)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "insert into taikhoan(tentaikhoan, matkhau, diachi, email, phone, loaitaikhoan)"
                + " values(@tentaikhoan, @matkhau, @diachi, @email, @phone, @loaitaikhoan)";
                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@tentaikhoan", SqlDbType.VarChar);
                param.Value = tk.TenTaiKhoan;
                command.Parameters.Add(param);

                param = new SqlParameter("@matkhau", SqlDbType.VarChar);
                param.Value = tk.MatKhau;
                command.Parameters.Add(param);

                param = new SqlParameter("@diachi", SqlDbType.VarChar);
                param.Value = tk.DiaChi;
                command.Parameters.Add(param);

                param = new SqlParameter("@email", SqlDbType.VarChar);
                param.Value = tk.Email;
                command.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.VarChar);
                param.Value = tk.Phone;
                command.Parameters.Add(param);

                param = new SqlParameter("@loaitaikhoan", SqlDbType.Int);
                param.Value = tk.LoaiTaiKhoan;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        #endregion

        #region cap nhat tai khoan
        public static bool updateAccount(TaiKhoanDTO tk)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "update taikhoan set tentaikhoan=@tentaikhoan, matkhau= @matkhau, diachi=@diachi, email=@email, phone= @phone, loaitaikhoan=@loaitaikhoan where id=@id";
                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@id", SqlDbType.VarChar);
                param.Value = tk.Id;
                command.Parameters.Add(param);

                param = new SqlParameter("@tentaikhoan", SqlDbType.VarChar);
                param.Value = tk.TenTaiKhoan;
                command.Parameters.Add(param);

                param = new SqlParameter("@matkhau", SqlDbType.VarChar);
                param.Value = tk.MatKhau;
                command.Parameters.Add(param);

                param = new SqlParameter("@diachi", SqlDbType.VarChar);
                param.Value = tk.DiaChi;
                command.Parameters.Add(param);

                param = new SqlParameter("@email", SqlDbType.VarChar);
                param.Value = tk.Email;
                command.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.VarChar);
                param.Value = tk.Phone;
                command.Parameters.Add(param);

                param = new SqlParameter("@loaitaikhoan", SqlDbType.Int);
                param.Value = tk.LoaiTaiKhoan;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        #endregion

        #region xoa tai khoan
        public static bool deleteAccount(TaiKhoanDTO tk)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "delete from taikhoan where tentaikhoan=@tentaikhoan";
                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@tentaikhoan", SqlDbType.VarChar);
                param.Value = tk.TenTaiKhoan;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        public static bool deleteAccount(string _username)
        {
            bool result = true;
            SqlConnection connect = null;
            try
            {
                connect = DataProvider.Connect();
                string strCommand = "delete from taikhoan where tentaikhoan=@tentaikhoan";
                SqlCommand command = new SqlCommand(strCommand, connect);

                SqlParameter param;
                param = new SqlParameter("@tentaikhoan", SqlDbType.VarChar);
                param.Value = _username;
                command.Parameters.Add(param);

                int n = command.ExecuteNonQuery();
                if (n == 0)
                {
                    result = false;
                }
            }
            catch (Exception ex)
            {
                result = false;
            }
            finally
            {
                if (connect != null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }
        #endregion
    }
}
